package cn.pb.news3.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;

import cn.pb.news3.util.ConfigManager;


public class News3Dao2 {
	Connection connection = null;
	Statement st = null;
	ResultSet rs = null;
	PreparedStatement pst;
	String driver = ConfigManager .getInstance().getString("jdbc.driver.class");
	String url = ConfigManager .getInstance().getString("jdbc.connection.url");
	String username = ConfigManager .getInstance().getString("jdbc.connection.username");
	String password = ConfigManager .getInstance().getString("jdbc.connection.password");
	// 查询新闻明细表
	public void getNews3List(){
		try {
			// 1、加载驱动
			Class.forName(driver);
			// 2、获取数据库连接
			connection = DriverManager.getConnection(url, username, password);
			// 3、获取statement对象，执行sql语句
			st = connection.createStatement();
			String sql = "select * from news_detail ";
			rs = st.executeQuery(sql); 
			// 4、处理sql执行结果
			while(rs.next()){
				int id = rs.getInt("id");
				int categoryId = rs.getInt("categoryId");
				String title = rs.getString("title");
				String summary = rs.getString("summary");
				String content = rs.getString("content");
				Timestamp createDate = rs.getTimestamp("createDate");
				String author = rs.getString("author");
				System.out.println("id:" + id + "\tcategoryId:" + categoryId + "\ttitle:" 
				+ title + "\tsummary:" + summary + "\tcontent:" 
				+ content + "\tcreateDate:" + createDate + "\tauthor:" + author);
				
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			// 5、释放资源
			try {
				rs.close();
				st.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// 增加新闻信息
	public void add(int id, int categoryId, String title, String summary, String content, Date createDate, String author){
		try {
			// 加载数据库驱动
			Class.forName(driver);
			// 获取数据库连接
			connection = DriverManager.getConnection(url, username, password);
			// 创建statement对象，执行sql语句
//			st = connection.createStatement();
//			String sql = "insert into news_detail(id,categoryId,title,summary,content,createDate,author) values("+id+","+categoryId+",'"+title+"','"+summary+"','"+content+"','"+new Timestamp(createDate.getTime())+"','"+author+"')";
			String sql = "insert into news_detail(id,categoryId,title,summary,content,createDate,author) values(?,?,?,?,?,?,?)";
			pst = connection.prepareStatement(sql);
			pst.setInt(1, id);
			pst.setInt(2, categoryId);
			pst.setString(3, title);
			pst.setString(4, summary);
			pst.setString(5, content);
			pst.setTimestamp(6, new Timestamp(createDate.getTime()));
			pst.setString(7, author);
			//int i = st.executeUpdate(sql);
			int i = pst.executeUpdate();
			// 处理执行结果
			if(i>0){
				System.out.println("插入成功");
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				pst.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	// 修改新闻信息
	public void update(int id, String title){
		try {
			// 加载数据库驱动
			Class.forName(driver);
			// 获取数据库连接
			connection = DriverManager.getConnection(url, username, password);
			// 获取prepareStatement对象，执行sql语句
			String sql = "update news_detail set title=? where id=?";
			pst = connection.prepareStatement(sql);
			pst.setString(1, title);
			pst.setInt(2, id);
			int flag = pst.executeUpdate();
			//处理执行结果
			if(flag > 0){
				System.out.println("修改成功！");
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pst.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
	}

	// 删除新闻信息
	public void delete(int id){
		try {
			//加载数据库驱动
			Class.forName(driver);
			// 获取数据库连接
			connection = DriverManager.getConnection(url, username, password);
			// 获取prepareStatement对象，执行sql语句
			String sql = "delete from news_detail where id=?";
			pst = connection.prepareStatement(sql);
			pst.setInt(1, id);
			int flag = pst.executeUpdate();
			// 处理执行结果
			if(flag > 0){
				System.out.println("删除成功!");
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pst.close();
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String[] args) {
		News3Dao2 news3Dao= new News3Dao2();
		//news3Dao.add(15, 13, "增加测试", "摘要", "内容" , new Date(), "admin");
		//news3Dao.update(11, "修改测试");
		news3Dao.delete(12);
		news3Dao.getNews3List();
	}
}
